<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Operations on a single entity type - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span></li>
<li class="tocentry current"><a class="current" href="SingleModelCrud.htm">Operations on a single entity type</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>
<li class="tocentry"><a href="MultipleCrud.htm">Operations on sets</a>
</li>
<li class="tocentry"><a href="ModelWithChildren.htm">Operations on a graph of multiple entity types</a>
</li>
<li class="tocentry"><a href="Immutable.htm">Operations on immutable entities</a>
</li>
<li class="tocentry"><a href="TryCrud.htm">Handling failures and exceptions</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="OperationswithaForeignKeybasedLookupTable.htm">Operations with a Foreign Key based Lookup Table</a></span>
</li>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></li> / <li><a href="SingleModelCrud.htm">Operations on a single entity type</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="single-model-crud">Single Model CRUD<a class="headerlink" href="#single-model-crud" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>This scenario covers the basic Create-Read-Update-Delete operations on a model that represents a single row in the database.</p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface ISingleModelCrudScenario&lt;TEmployeeClassification&gt;
   where TEmployeeClassification : class, IEmployeeClassification, new()
{
    /// &lt;summary&gt;
    /// Create a new EmployeeClassification row, returning the new primary key.
    /// &lt;/summary&gt;
    int Create(TEmployeeClassification classification);

    /// &lt;summary&gt;
    /// Delete a EmployeeClassification row using an object.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void Delete(TEmployeeClassification classification);

    /// &lt;summary&gt;
    /// Delete a EmployeeClassification row using its primary key.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void DeleteByKey(int employeeClassificationKey);

    /// &lt;summary&gt;
    /// Gets an EmployeeClassification row by its name. Assume the name is unique.
    /// &lt;/summary&gt;
    TEmployeeClassification? FindByName(string employeeClassificationName);

    /// &lt;summary&gt;
    /// Gets all EmployeeClassification rows.
    /// &lt;/summary&gt;
    IList&lt;TEmployeeClassification&gt; GetAll();

    /// &lt;summary&gt;
    /// Gets an EmployeeClassification row by its primary key.
    /// &lt;/summary&gt;
    TEmployeeClassification? GetByKey(int employeeClassificationKey);

    /// &lt;summary&gt;
    /// Update a EmployeeClassification row.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void Update(TEmployeeClassification classification);
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>With ADO.NET, the model does not actually participate in database operations so it needs no adornment.</p>
<pre><code class="cs">public class EmployeeClassification : IEmployeeClassification
{
    public EmployeeClassification()
    {
    }

    public EmployeeClassification(IDataReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException(nameof(reader), $&quot;{nameof(reader)} is null.&quot;);

        EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;));
        EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;));
        IsExempt = reader.GetBoolean(reader.GetOrdinal(&quot;IsExempt&quot;));
        IsEmployee = reader.GetBoolean(reader.GetOrdinal(&quot;IsEmployee&quot;));
    }

    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}
</code></pre>

<p>The repository methods use raw SQL strings. All other ORMs internally generate the same code. </p>
<h3 id="sql-server">SQL Server<a class="headerlink" href="#sql-server" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h3>
<p>To return a primary key from an <code>INSERT</code> statement, use <code>OUTPUT Inserted.EmployeeClassificationKey</code>.</p>
<pre><code class="cs">public class SingleModelCrudScenario : SqlServerScenarioBase, ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public SingleModelCrudScenario(string connectionString) : base(connectionString)
    { }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, classification.EmployeeClassificationName);
            return (int)cmd.ExecuteScalar();
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, classification.EmployeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, employeeClassificationName);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                };
            }
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;&quot;;

        var result = new List&lt;EmployeeClassification&gt;();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                result.Add(new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                });
            }
            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                };
            }
        }
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, classification.EmployeeClassificationKey);
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, classification.EmployeeClassificationName);
            cmd.ExecuteNonQuery();
        }
    }
}
</code></pre>

<h3 id="postgresql">PostgreSQL<a class="headerlink" href="#postgresql" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h3>
<p>To return a primary key from an <code>INSERT</code> statement, use <code>RETURNING EmployeeClassificationKey</code>.</p>
<pre><code class="cs">public class SingleModelCrudPostgreSqlScenario : PostgreSqlScenarioBase, ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public SingleModelCrudPostgreSqlScenario(string connectionString) : base(connectionString)
    { }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    VALUES(@EmployeeClassificationName )
                    RETURNING EmployeeClassificationKey&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, classification.EmployeeClassificationName);
            return (int)cmd.ExecuteScalar()!;
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;DELETE FROM HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, classification.EmployeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;DELETE FROM HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
            cmd.ExecuteNonQuery();
        }
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, employeeClassificationName);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                };
            }
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;&quot;;

        var result = new List&lt;EmployeeClassification&gt;();

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                result.Add(new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                });
            }
            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal(&quot;EmployeeClassificationKey&quot;)),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal(&quot;EmployeeClassificationName&quot;))
                };
            }
        }
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
        using (var cmd = new NpgsqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, classification.EmployeeClassificationKey);
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;, classification.EmployeeClassificationName);
            cmd.ExecuteNonQuery();
        }
    }
}
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>Strictly speaking, Chain can use the same models as ADO.NET and Dapper so long as the column and property names match. However, it is more convenient to tag the class with what table it refers to.</p>
<pre><code class="cs">[Table(&quot;HR.EmployeeClassification&quot;)]
public class EmployeeClassification : IEmployeeClassification
{
    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}
</code></pre>

<p>Without the Table attribute, the table name will have to be specified in every call in the repository.</p>
<p>Other information such as primary keys are read from the database's metadata.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    readonly SqlServerDataSource m_DataSource;

    public SingleModelCrudScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        return m_DataSource.Insert(classification).ToInt32().Execute();
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        m_DataSource.Delete(classification).Execute();
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        m_DataSource.DeleteByKey&lt;EmployeeClassification&gt;(employeeClassificationKey).Execute();
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        return m_DataSource.From&lt;EmployeeClassification&gt;(new { employeeClassificationName })
            .ToObject().Execute();
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        return m_DataSource.From&lt;EmployeeClassification&gt;().ToCollection().Execute();
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey&lt;EmployeeClassification&gt;(employeeClassificationKey)
            .ToObject().Execute();
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        m_DataSource.Update(classification).Execute();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>Dapper is essentially just ADO.NET with some helper methods to reduce the amount of boilerplate code.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ScenarioBase, ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public SingleModelCrudScenario(string connectionString) : base(connectionString)
    {
    }

    virtual public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        var sql = @&quot;INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )&quot;;

        using (var con = OpenConnection())
            return con.ExecuteScalar&lt;int&gt;(sql, classification);
    }

    virtual public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        var sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
            con.Execute(sql, classification);
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        var sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
            con.Execute(sql, new { employeeClassificationKey });
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        var sql = @&quot;SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @EmployeeClassificationName;&quot;;

        using (var con = OpenConnection())
            return con.QuerySingle&lt;EmployeeClassification&gt;(sql, new { employeeClassificationName });
    }

    virtual public IList&lt;EmployeeClassification&gt; GetAll()
    {
        var sql = @&quot;SELECT  ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;&quot;;

        using (var con = OpenConnection())
            return con.Query&lt;EmployeeClassification&gt;(sql).ToList();
    }

    virtual public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
            return con.QuerySingle&lt;EmployeeClassification&gt;(sql, new { employeeClassificationKey });
    }

    virtual public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        var sql = @&quot;UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        using (var con = OpenConnection())
            con.Execute(sql, classification);
    }
}
</code></pre>

<div class="alert alert-info"><span class="alert-title"><i class="fa fa-info-circle"></i> Info</span><p>The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.</p>
</div><h3 id="dapper.contrib">Dapper.Contrib<a class="headerlink" href="#dapper.contrib" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h3>
<p>The Dapper.Contrib library can elimiante the boilerplate for some common scenarios. </p>
<p>To enable it, models need to be decorated with <code>Table</code> and <code>Key</code> attributes. </p>
<pre><code class="cs">[Table(&quot;HR.EmployeeClassification&quot;)]
public class EmployeeClassification : IEmployeeClassification
{
    //Table and Key attributes are only used by Dapper.Contrib.
    //They are not needed in the Dapper-only examples.

    [Key]
    public int EmployeeClassificationKey { get; set; }

    public string? EmployeeClassificationName { get; set; }
    public bool IsEmployee { get; set; }
    public bool IsExempt { get; set; }
}
</code></pre>

<pre><code class="cs">public class SingleModelCrudScenarioContrib : SingleModelCrudScenario
{
    public SingleModelCrudScenarioContrib(string connectionString) : base(connectionString)
    {
    }

    override public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var con = OpenConnection())
            return (int)con.Insert(classification);
    }

    override public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var con = OpenConnection())
            con.Delete(classification);
    }

    override public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var con = OpenConnection())
            return con.GetAll&lt;EmployeeClassification&gt;().ToList();
    }

    override public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
            return con.Get&lt;EmployeeClassification&gt;(employeeClassificationKey);
    }

    override public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var con = OpenConnection())
            con.Update(classification);
    }
}
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>The DbConnector library leverages ADO.NET and reduces the amount of boilerplate code required when projecting relational data into objects.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ScenarioBase, ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public SingleModelCrudScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )&quot;;

        return DbConnector.Scalar&lt;int&gt;(sql, classification).Execute();
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        DbConnector.NonQuery(sql, classification).Execute();
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;DELETE HR.EmployeeClassification WHERE EmployeeClassificationKey = @employeeClassificationKey;&quot;;

        DbConnector.NonQuery(sql, new { employeeClassificationKey }).Execute();
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationName = @employeeClassificationName;&quot;;

        return DbConnector.ReadSingle&lt;EmployeeClassification&gt;(sql, new { employeeClassificationName }).Execute();
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName FROM HR.EmployeeClassification ec;&quot;;

        return DbConnector.ReadToList&lt;EmployeeClassification&gt;(sql).Execute();
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @employeeClassificationKey;&quot;;

        return DbConnector.ReadSingle&lt;EmployeeClassification&gt;(sql, new { employeeClassificationKey }).Execute();
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        const string sql = @&quot;UPDATE HR.EmployeeClassification
                    SET EmployeeClassificationName = @EmployeeClassificationName
                    WHERE EmployeeClassificationKey = @EmployeeClassificationKey;&quot;;

        DbConnector.NonQuery(sql, classification).Execute();
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public SingleModelCrudScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.EmployeeClassification.Add(classification);
            context.SaveChanges();
            return classification.EmployeeClassificationKey;
        }
    }

    public virtual void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassification.Find(employeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassification.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.Where(ec =&gt; ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.ToList();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassification.Find(employeeClassificationKey);
        }
    }

    public virtual void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            //Get a fresh copy of the row from the database
            var temp = context.EmployeeClassification.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                //Copy the changed fields
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                context.SaveChanges();
            }
        }
    }
}
</code></pre>

<div class="alert alert-info"><span class="alert-title"><i class="fa fa-info-circle"></i> Info</span><p>The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.</p>
</div><h3 id="entity-framework-6---improved">Entity Framework 6 - Improved<a class="headerlink" href="#entity-framework-6---improved" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h3>
<p>The design of Entity Framework 6 requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.</p>
<pre><code class="cs">public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public SingleModelCrudScenario2(Func&lt;OrmCookbookContext&gt; dBContextFactory) : base(dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public override void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public override void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
            context.Entry(temp).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public override void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}
</code></pre>

<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>To use Entity Framework, one needs to create a DbContext class. Here is a minimal example:</p>
<pre><code class="csharp">public partial class OrmCookbook : DbContext
{
    public OrmCookbook()
        : base(&quot;name=OrmCookbook&quot;)
    {
    }

    public virtual DbSet&lt;EmployeeClassification&gt; EmployeeClassifications { get; set; }

}
</code></pre>

<p>Depending on how you setup the DbContext, the model requires some further annotations such as which table it applies to and what the primary key is.</p>
<pre><code class="csharp">[Table(&quot;HR.EmployeeClassification&quot;)]
public partial class EmployeeClassification
{
    [Key]
    public int EmployeeClassificationKey { get; set; }

    [StringLength(30)]
    public string EmployeeClassificationName { get; set; }
}
</code></pre>

<p>The context and model can be generated for you from the database using Entity Framework’s “Code First” tooling. (The name “code first” doesn’t literally mean the code has to be written before the database. Rather, it really means that you are not using EDMX style XML files.) For more information see https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx</p>
<p>Finally, there is the repository itself:</p>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public SingleModelCrudScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.EmployeeClassifications.Add(classification);
            context.SaveChanges();
            return classification.EmployeeClassificationKey;
        }
    }

    public virtual void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassifications.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassifications.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            //Find the row you wish to delete
            var temp = context.EmployeeClassifications.Find(employeeClassificationKey);
            if (temp != null)
            {
                context.EmployeeClassifications.Remove(temp);
                context.SaveChanges();
            }
        }
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.Where(ec =&gt; ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.ToList();
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.Find(employeeClassificationKey);
        }
    }

    public virtual void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            //Get a fresh copy of the row from the database
            var temp = context.EmployeeClassifications.Find(classification.EmployeeClassificationKey);
            if (temp != null)
            {
                //Copy the changed fields
                temp.EmployeeClassificationName = classification.EmployeeClassificationName;
                context.SaveChanges();
            }
        }
    }
}
</code></pre>

<div class="alert alert-info"><span class="alert-title"><i class="fa fa-info-circle"></i> Info</span><p>The repository methods are not normally virtual. This was done so that they could be overridden with better implementations as shown below.</p>
</div><h3 id="entity-framework-core---improved">Entity Framework Core - Improved<a class="headerlink" href="#entity-framework-core---improved" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h3>
<p>The design of Entity Framework Core requires extraneous database calls when performing an update or delete operation. This revised version eliminates the extra calls.</p>
<pre><code class="cs">public class SingleModelCrudScenario2 : SingleModelCrudScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public SingleModelCrudScenario2(Func&lt;OrmCookbookContext&gt; dBContextFactory) : base(dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public override void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public override void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            //var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
            //context.Entry(temp).State = EntityState.Deleted;
            context.EmployeeClassifications.Remove(new EmployeeClassification { EmployeeClassificationKey = employeeClassificationKey });
            context.SaveChanges();
        }
    }

    public override void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = new OrmCookbook())
        {
            return db.InsertWithInt32Identity(classification);
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = new OrmCookbook())
        {
            db.EmployeeClassification
                .Where(d =&gt; d.EmployeeClassificationKey == classification.EmployeeClassificationKey)
                .Delete();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            db.EmployeeClassification
                .Where(d =&gt; d.EmployeeClassificationKey == employeeClassificationKey)
                .Delete();
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var db = new OrmCookbook())
        {
            var query = from ec in db.EmployeeClassification
                        where ec.EmployeeClassificationName == employeeClassificationName
                        select ec;
            return query.Single();
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var db = new OrmCookbook())
        {
            return db.EmployeeClassification.ToList();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            return db.EmployeeClassification.Where(d =&gt; d.EmployeeClassificationKey == employeeClassificationKey).Single();
        }
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = new OrmCookbook())
        {
            db.Update(classification);
        }
    }
}
</code></pre>

<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>LLBLGen Pro offers multiple ways to perform CRUD operations: via entity instances or directly on the data in the database, to avoid a fetch of entities first. The code below illustrates this and multiple query systems. Entity types are derived from a common base class.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassificationEntity&gt;
{
    public int Create(EmployeeClassificationEntity classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            adapter.SaveEntity(classification, true, recurse: false);
            return classification.EmployeeClassificationKey;
        }
    }

    public virtual void Delete(EmployeeClassificationEntity classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            // flag the entity as not-new, so we can delete it without fetching it first if the PK is set.
            classification.IsNew = false;
            adapter.DeleteEntity(classification);
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            // delete directly, so we don't have to fetch the entity first.
            adapter.DeleteEntitiesDirectly(typeof(EmployeeClassificationEntity),
                                           new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                                       .Equal(employeeClassificationKey)));
        }
    }

    public EmployeeClassificationEntity FindByName(string employeeClassificationName)
    {
        using (var adapter = new DataAccessAdapter())
        {
            // let's use QuerySpec
            return adapter.FetchFirst(new QueryFactory().EmployeeClassification
                                            .Where(EmployeeClassificationFields.EmployeeClassificationName
                                                                               .Equal(employeeClassificationName)));
        }
    }

    public IList&lt;EmployeeClassificationEntity&gt; GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            // you know what, let's use the low level API for a change.
            var toReturn = new EntityCollection&lt;EmployeeClassificationEntity&gt;();
            adapter.FetchEntityCollection(toReturn, null);
            return toReturn;
        }
    }

    public EmployeeClassificationEntity? GetByKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeClassification
                                .FirstOrDefault(ec =&gt; ec.EmployeeClassificationKey == employeeClassificationKey);
        }
    }

    public virtual void Update(EmployeeClassificationEntity classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var adapter = new DataAccessAdapter())
        {
            // re-use existing entity (as it tracks changes internally) otherwise fetch the instance from the DB
            EmployeeClassificationEntity toPersist = classification;
            if (classification.IsNew)
            {
                toPersist = adapter.FetchNewEntity&lt;EmployeeClassificationEntity&gt;(
                            new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                                                                    .Equal(classification.EmployeeClassificationKey)));
                toPersist.EmployeeClassificationName = classification.EmployeeClassificationName;
            }
            if (!toPersist.IsNew)
            {
                adapter.SaveEntity(toPersist, refetchAfterSave: false, recurse: false);
            }
        }
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public partial class EmployeeClassification : IEmployeeClassification
{
    public virtual int EmployeeClassificationKey { get; set; }

    public virtual string? EmployeeClassificationName { get; set; }
    public virtual bool IsEmployee { get; set; }
    public virtual bool IsExempt { get; set; }
}
</code></pre>

<p>Instead of attributes, a mapping file is used to associate the model with a database table. There is one file per table and each is set to <code>Build Action: Embedded resource</code>. </p>
<pre><code class="xml">&lt;hibernate-mapping
  assembly=&quot;Recipes.NHibernate&quot;
  namespace=&quot;Recipes.NHibernate.Entities&quot;&gt;
  &lt;!-- more mapping info here --&gt;
  &lt;class
    name=&quot;EmployeeClassification&quot;
    table=&quot;EmployeeClassification&quot;
    schema=&quot;HR&quot;&gt;
    &lt;id
      name=&quot;EmployeeClassificationKey&quot;&gt;
      &lt;generator
        class=&quot;native&quot; /&gt;
    &lt;/id&gt;
    &lt;property
      name=&quot;EmployeeClassificationName&quot; /&gt;
    &lt;property
      name=&quot;IsExempt&quot; /&gt;
    &lt;property
      name=&quot;IsEmployee&quot; /&gt;
  &lt;/class&gt;
&lt;/hibernate-mapping&gt;
</code></pre>

<p>A <code>SessionFactory</code> is needed to stitch the various configuration files together. </p>
<pre><code class="cs">private static void ConfigureSessionFactory()
{
    var jsonConfiguration = new ConfigurationBuilder().SetBasePath(AppContext.BaseDirectory).AddJsonFile(&quot;appsettings.json&quot;).Build();
    var sqlServerConnectionString = jsonConfiguration.GetSection(&quot;ConnectionStrings&quot;)[&quot;SqlServerTestDatabase&quot;];

    var configuration = new Configuration();
    configuration.Configure();

    configuration.SetProperty(NHibernateCfg.Environment.ConnectionString, sqlServerConnectionString);

    configuration.AddAssembly(typeof(Setup).Assembly);
    SessionFactory = configuration.BuildSessionFactory();

    try
    {
        (new Setup()).Warmup();
    }
    catch { }
}
</code></pre>

<p>Finally there is the repository itself.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    readonly ISessionFactory m_SessionFactory;

    public SingleModelCrudScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(classification);
            session.Flush();
            return classification.EmployeeClassificationKey;
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(classification);
            session.Flush();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey });
            session.Flush();
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver&lt;EmployeeClassification&gt;().Where(ec =&gt; ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            return session.QueryOver&lt;EmployeeClassification&gt;().List();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.Get&lt;EmployeeClassification&gt;(employeeClassificationKey);
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Update(classification);
            session.Flush();
        }
    }
}
</code></pre>

<p>The rules on when you need to call <code>Flush</code> are complex. In some cases it will be called for you implicitly, but as a general rule you need to invoke it before leaving a block that includes modifications.</p>
<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>When calling the <em>raw-SQL</em> operations, just like <em>Dapper</em>, RepoDB requires annotations on the classes. These are specific to RepoDb, you cannot use the standard <code>Table</code>, <code>Column</code>, and <code>Key</code> attributes from .NET.</p>
<pre><code class="cs">[Map(&quot;[HR].[EmployeeClassification]&quot;)]
public class EmployeeClassification : IEmployeeClassification
{
    public EmployeeClassification()
    {
    }

    public EmployeeClassification(IReadOnlyEmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        EmployeeClassificationKey = classification.EmployeeClassificationKey;
        EmployeeClassificationName = classification.EmployeeClassificationName;
        IsExempt = classification.IsExempt;
        IsEmployee = classification.IsEmployee;
    }

    public int EmployeeClassificationKey { get; set; }

    public string? EmployeeClassificationName { get; set; }

    public bool IsEmployee { get; set; }

    public bool IsExempt { get; set; }

    internal ReadOnlyEmployeeClassification ToImmutable()
    {
        return new ReadOnlyEmployeeClassification(this);
    }
}
</code></pre>

<p>The repository resembles Dapper, but with far less SQL.</p>
<pre><code class="cs">public class SingleModelCrudScenario : BaseRepository&lt;EmployeeClassification, SqlConnection&gt;,
    ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    public SingleModelCrudScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        return Insert&lt;int&gt;(classification);
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        base.Delete(classification);
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        Delete(employeeClassificationKey);
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        return Query(e =&gt; e.EmployeeClassificationName == employeeClassificationName).FirstOrDefault();
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        return QueryAll().AsList();
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        return Query(employeeClassificationKey).FirstOrDefault();
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        base.Update(classification);
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>ServiceStack requires the use of annotations on its models. These are specific to ServiceStack, you cannot use the standard <code>Table</code>, <code>Column</code>, and <code>Key</code> attributes from .NET.</p>
<pre><code class="cs">[Alias(&quot;EmployeeClassification&quot;), Schema(&quot;HR&quot;)]
public partial class EmployeeClassification
{
    [PrimaryKey, AutoIncrement, Alias(&quot;EmployeeClassificationKey&quot;)]
    public int Id { get; set; }

    [Required, StringLength(30)]
    public string? EmployeeClassificationName { get; set; }

    public bool IsExempt { get; set; }

    public bool IsEmployee { get; set; }

    [Reference]
    [SuppressMessage(&quot;Usage&quot;, &quot;CA2227:Collection properties should be read only&quot;, Justification = &quot;Required by ServiceStack&quot;)]
    public List&lt;Employee&gt; Employees { get; set; } = new List&lt;Employee&gt;();
}
</code></pre>

<p>The repository resemebles Dapper, but with far less SQL.</p>
<pre><code class="cs">public class SingleModelCrudScenario : ISingleModelCrudScenario&lt;EmployeeClassification&gt;
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public SingleModelCrudScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return (int)db.Insert(classification, true);
        }
    }

    public virtual void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete(classification);
        }
    }

    public virtual void DeleteByKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.DeleteById&lt;EmployeeClassification&gt;(employeeClassificationKey);
        }
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.Single&lt;EmployeeClassification&gt;(
                r =&gt; r.EmployeeClassificationName == employeeClassificationName);
        }
    }

    public IList&lt;EmployeeClassification&gt; GetAll()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.Select&lt;EmployeeClassification&gt;();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return db.SingleById&lt;EmployeeClassification&gt;(employeeClassificationKey);
        }
    }

    public virtual void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Update(classification);
        }
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
